﻿using System;
using System.Linq;
using System.Text;
using System.Collections;
using System.Collections.Generic;

using LeanEngine.Entity;
using LeanEngine.Extensions;
using LeanEngine.Collections;


namespace LeanEngine.Data.SqlClient
{
    internal sealed class SqlClientCrudCommand : ICrudCommand
    {
        #region Constructor Members

        internal SqlClientCrudCommand()
        {
            this.SqlTextBuilder = new SqlClientTextBuilder();
        }

        #endregion

        #region ICrudCommand Members

        /// <summary>
        /// Gets the builder of the sql command texts.
        /// </summary>
        public ISqlTextBuilder SqlTextBuilder { get; private set; }

        public void ExecuteInsert(PlainEntity value)
        {
            var entityType = value.GetType();
            var table = MetaTableCache.GetItem(value.GetType());

            using (var cmd = new DataCommand())
            {                
                cmd.CommandText = SqlTextBuilder.BuildInsertCommand(table.TableName, table.ColumnsList);

                foreach (var column in table.ColumnsList.WhereNotDbGenerated())
                {
                    var columnValue = column.GetValue(value);
                    if ((columnValue as PlainEntity) != null)
                    {
                        var fkTable = MetaTableCache.GetItem(columnValue.GetType());
                        foreach (var fkColumn in fkTable.ColumnsList.WherePrimaryKey())
                        {
                            cmd.Parameters.Add(cmd.NewParameter(column.DBColumn.Name, fkColumn.GetValue(columnValue)));
                        }
                    }
                    else
                    {
                        cmd.Parameters.Add(cmd.NewParameter(column.DBColumn.Name, columnValue));
                    }
                }

                var newID = cmd.ExecuteScalar();
                var identityColumn = table.ColumnsList.GetDbGeneratedList().FirstOrDefault();
                if (newID.IsNotNull() && identityColumn.DataType.IsNotNull())
                {
                    identityColumn.SetValue(value, identityColumn.ChangeType(newID));
                }
            }
        }

        public void ExecuteInsertRelation(string table, PlainEntity value, IList relationList)
        {
            if (relationList.IsNotNullOrEmpty())
            {
                using (var cmd = new SqlClientInsertRelationCommand())
                {
                    cmd.ExecuteNonQuery(table, value, relationList);
                }
            }
        }

        public void ExecuteUpdate(PlainEntity value)
        {
            var table = MetaTableCache.GetItem(value.GetType());
            if (table.ColumnsList.WherePrimaryKey().Count() == 0)
            {
                throw new System.Data.DataException("This operation doesn't work with entities without primary keys.");
            }

            var changeTracker = value as ChangeTrackerEntity;
            if (changeTracker == null)
            {
                ExecuteUpdatePlainEntity(value, table);
            }
            else
            {
                ExecuteUpdateChangeTrackerEntity(changeTracker, table);
            }
        }

        public void ExecuteDelete(PlainEntity value)
        {
            var table = MetaTableCache.GetItem(value.GetType());
            if (table.ColumnsList.WherePrimaryKey().Count() == 0)
            {
                throw new System.Data.DataException("This operation doesn't work with entities without primary keys.");
            }

            using (var cmd = new DataCommand())
            {
                cmd.CommandText = this.SqlTextBuilder.BuildDeleteCommand(table.TableName, table.ColumnsList.WherePrimaryKey());
                foreach (var column in table.ColumnsList.WherePrimaryKey())
                {
                    cmd.Parameters.Add(cmd.NewParameter(column.DBColumn.Name, column.GetValue(value)));
                }

                cmd.ExecuteNonQuery();
            }
        }

        public void ExecuteDeleteRelation(string table, PlainEntity value, IList relationList)
        {
            if (relationList.IsNotNullOrEmpty())
            {
                using (var cmd = new SqlClientDeleteRelationCommand())
                {
                    cmd.ExecuteNonQuery(table, value, relationList);
                }
            }
        }

        public long ExecuteCount(Type entityType)
        {
            var table = MetaTableCache.GetItem(entityType);
            using (var cmd = new DataCommand())
            {
                cmd.CommandText = SqlTextBuilder.BuildCountCommand(table.TableName);
                var countValue = cmd.ExecuteScalar();

                return countValue.IsNotNull() ? countValue.ToInt64() : -1;
            }
        }

        public LeanEngine.Collections.PagedList<T> ExecuteReader<T>(int pageIndex, int pageSize) 
            where T : PlainEntity, new()
        {
            var table = MetaTableCache.GetItem(typeof(T));
            var pagedList = new PagedList<T>()
            {
                PageIndex = pageIndex,
                PageSize = pageSize
            };

            using (var cmd = new DataCommand())
            {
                cmd.CommandText = this.SqlTextBuilder.BuildPagedListCommand(table.TableName, table.ColumnsList.WherePrimaryKey());
                cmd.Parameters.Add(cmd.NewParameter("PageIndex", (pageIndex * pageSize) + 1));
                cmd.Parameters.Add(cmd.NewParameter("PageSize", (pageIndex * pageSize) + pageSize));

                pagedList.Items = cmd.ExecuteReader<T>();
            }

            return pagedList;
        }

        public PlainEntity ExecuteReader(PlainEntity value)
        {
            var table = MetaTableCache.GetItem(value.GetType());
            using (var cmd = new DataCommand())
            {
                cmd.CommandText = this.SqlTextBuilder.BuildReadCommand(table.TableName, table.ColumnsList.WherePrimaryKey());
                foreach (var column in table.ColumnsList.WherePrimaryKey())
                {
                    cmd.Parameters.Add(cmd.NewParameter(column.DBColumn.Name, column.GetValue(value)));
                }

                using (var reader = cmd.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        value.Fill(reader);
                    }
                    else
                    {
                        value = (PlainEntity)table.EntityType.Assembly.CreateInstance(table.EntityType.FullName);
                    }
                }
            }

            return value;
        }

        #endregion

        #region Method Members

        private static void ExecuteUpdatePlainEntity(PlainEntity value, MetaTable metaTable)
        {
            using (var cmd = new DataCommand())
            {
                cmd.CommandText = "UPDATE " + metaTable.TableName + " SET ";
                foreach (var column in metaTable.ColumnsList.WherePrimaryKey())
                {
                    var columnValue = column.GetValue(value);
                    var fkTable = MetaTableCache.GetItem(columnValue.GetType());
                    if (columnValue is PlainEntity)
                    {
                        foreach (var fkColumn in fkTable.ColumnsList.WherePrimaryKey())
                        {
                            cmd.CommandText += fkColumn.DBColumn.Name + " = @" + fkColumn.DBColumn.Name + ", ";
                            cmd.Parameters.Add(cmd.NewParameter(fkColumn.DBColumn.Name, fkColumn.GetValue(columnValue)));
                        }
                    }
                    else
                    {
                        cmd.CommandText += column.DBColumn.Name + " = @" + column.DBColumn.Name + ", ";
                        cmd.Parameters.Add(cmd.NewParameter(column.DBColumn.Name, column.GetValue(value)));
                    }
                }

                cmd.CommandText = cmd.CommandText.Substring(0, cmd.CommandText.Length - 2) + " WHERE ";

                foreach (var column in metaTable.ColumnsList.WherePrimaryKey())
                {
                    cmd.CommandText += column.DBColumn.Name + " = @" + column.DBColumn.Name + ", ";
                    cmd.Parameters.Add(cmd.NewParameter(column.DBColumn.Name, column.GetValue(value)));
                }

                cmd.CommandText = cmd.CommandText.Substring(0, cmd.CommandText.Length - 2);

                cmd.ExecuteNonQuery();
            }
        }

        private static void ExecuteUpdateChangeTrackerEntity(ChangeTrackerEntity value, MetaTable metaTable)
        {
            using (var cmd = new DataCommand())
            {
                cmd.CommandText = "UPDATE " + metaTable.TableName + " SET ";
                foreach (var column in value.ChangedProperties)
                {
                    var columnValue = column.GetValue(value);
                    var fkTable = MetaTableCache.GetItem(columnValue.GetType());
                    if (columnValue is PlainEntity)
                    {
                        foreach (var fkColumn in fkTable.ColumnsList.WherePrimaryKey())
                        {
                            cmd.CommandText += fkColumn.DBColumn.Name + " = @" + fkColumn.DBColumn.Name + ", ";
                            cmd.Parameters.Add(cmd.NewParameter(fkColumn.DBColumn.Name, fkColumn.GetValue(columnValue)));
                        }
                    }
                    else
                    {
                        cmd.CommandText += column.DBColumn.Name + " = @" + column.DBColumn.Name + ", ";
                        cmd.Parameters.Add(cmd.NewParameter(column.DBColumn.Name, column.GetValue(value)));
                    }
                }

                cmd.CommandText += "DataVersion = DataVersion + 1 WHERE DataVersion = @DataVersion";

                cmd.Parameters.Add(cmd.NewParameter("DataVersion", value.DataVersion));

                cmd.ExecuteNonQuery();
            }
        }

        #endregion
    }
}
